#coding=utf-8

import sqlite3
import os

class SqliteTool:
	def __init__(self):
		self.db = sqlite3.connect(os.getcwd() + "\Data.db")
		self.db.execute('create table if not exists ISOInfoTable(ID INTEGER PRIMARY KEY, MD5 varchar(32),DirName varchar(40),ISOName varchar(80),PageLink varchar(255),DownloadLink varchar(255),SoftSize varchar(8),UpdateTime varchar(20))')#ISO信息存储表
		#self.db.execute('create table if not exists DownloadingISOTable(ID INTEGER PRIMARY KEY,DirName varchar(40), DownloadLink varchar(255))')#正在下载ISO存储表(存储正在下载镜像下载链接)
		self.db.execute('create table if not exists DownloadedISOTable(ID INTEGER PRIMARY KEY, DownloadLink varchar(255))')#已下载ISO存储表(存储已下载镜像下载链接)

	def __del__(self):
		self.db.close()

	#判断ISO是否重复
	def IsISOInfoExist(self,downloadLink):
		cur = self.db.cursor()
		#判断是否是已下载镜像
		cur.execute("select * from DownloadedISOTable where DownloadLink='"+downloadLink+"'")
		self.db.commit()
		if cur.fetchall():
			return True
 		#判断是否存在于待下载镜像中
		cur.execute("select * from ISOInfoTable where DownloadLink='"+downloadLink+"'")
		self.db.commit()
		if not cur.fetchall():#此ISO信息不存在
			cur.close()
			return False
		else:#此ISO信息存在
			cur.close()
			return True

	#存储ISO信息
	def SaveISOInfo(self,md5,dirName,isoName,pageLink,downloadLink,softSize,updateTime):
		cur = self.db.cursor()
		cur.execute('insert into ISOInfoTable(MD5,dirName,ISOName,PageLink,DownloadLink,SoftSize,UpdateTime) values("'+md5+'","'+dirName+'","'+isoName+'","'+pageLink+'","'+downloadLink+'","'+softSize+'","'+updateTime+'")')
		self.db.commit()
		cur.close()
	
	#取出下一条未下载完ISO信息供下载
	#def FetchNextDownloadingISOInfo(self):
	#	cur = self.db.cursor()
	#	cur.execute("select count(*) from DownloadingISOTable")
	#	self.db.commit()
	#	values = cur.fetchall()
	#	if values[0][0]==0:#空数据
	#		return False
	#	cur.execute("select * from DownloadingISOTable LIMIT 1")
	#	self.db.commit()
	#	values = cur.fetchall()
		#cur.execute("delete from ISOInfoTable where ID="+values[0][0])
		#self.db.commit()
	#	cur.close()
	#	return values[0]

	#取出下一条ISO信息供下载
	def FetchNextISOInfo(self,downloadingList):
		cur = self.db.cursor()
		cur.execute("select count(*) from ISOInfoTable")
		self.db.commit()
		values = cur.fetchall()
		if values[0][0]==0:#空数据
			cur.close()
			return False

		row = 0
		values = None
		#判断下一条镜像是否在当前下载列表当中，如果在其中，查询再下一条
		while True:
			cur.execute("select * from ISOInfoTable LIMIT 1 OFFSET "+str(row))
			self.db.commit()
			values = cur.fetchall()
			if not values[0]:
				cur.close()
				return False
			isDownloading = False
			for Link in downloadingList:
				if values[0][5]==Link:
					isDownloading = True
			if isDownloading:
				row += 1
			else:
				break
		#cur.execute("delete from ISOInfoTable where ID="+str(values[0][0]))
		#self.db.commit()
		cur.close()
		return values[0]

	#保存正在下载镜像
	#def SaveDownloadingISO(self,dirname,downloadLink):
	#	cur = self.db.cursor()
	#	cur.execute("insert into DownloadingISOTable(Dirname,DownloadLink) values('"+dirname+"','"+downloadLink+"')")
	#	self.db.commit()
	#	cur.close()

	#保存已下载镜像
	def SaveDownloadedISO(self,downloadLink):
		cur = self.db.cursor()
		cur.execute("delete from ISOInfoTable where DownloadLink="+downloadLink)
		self.db.commit()
		cur.execute("insert into DownloadedISOTable(DownloadLink) values('"+downloadLink+"')")
		self.db.commit()
		#cur.execute("delete from DownloadingISOTable where DownloadLink='"+downloadLink+"'")
		#self.db.commit()
		cur.close()
